package gbench.sandbox;

import org.junit.Test;

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Pattern;

import gbench.common.fs.FileSystem;
import gbench.common.fs.XlsFile.SimpleExcel;
import gbench.common.matlib.algebra.lisp.DFrame;
import static gbench.common.matlib.algebra.lisp.IRecord.*;
import com.alibaba.druid.sql.SQLUtils;


/**
 * count
 * rank
 * keys
 * record{kvps}
 * 
 * @author xuqinghua
 *
 */
public class JunitPingAnBankSqlCheck {

	/**
	 * 
	 * @param objects
	 */
	public static void println(final Object... objects) {
		System.out.println(Arrays.asList(objects));
	}

	/**
	 * 提取sql脚本
	 * 
	 * @param line 数据行
	 * @return String
	 */
	public final String extract_sql(String line) {
		var _line = line.replaceAll("<!--[^-]+-->", ""); // 去除 注释项目
		_line = _line.replaceAll("<[^>]+>", ""); // 去除 标签标记
		_line = _line.replaceAll("^\"+", ""); // 去除开头的引号
		_line = _line.replaceAll("\"+$", ""); // 去除结尾的引号
		_line = FT("select count(*) cnt from ( $0 ) t_pingan_cnt", _line.strip());
		final var keywords = this.extract_keywords(_line);
		println("keyword",keywords);
		try {
			_line = SQLUtils.format(_line, "mysql");
		} catch (Exception e) {
			System.err.println(_line);
		}
		return _line.strip();
	}
	
	/**
	 * 
	 * @param line
	 * @return
	 */
	public final List<String> extract_keywords(final String line) {
		final var pattern = Pattern.compile("#\\{[^}]+}");
		final var matcher = pattern.matcher(line);
		final var ll = new ArrayList<String>();
		while (matcher.find()) {
			ll.add(matcher.group());
		}
		return ll;
	}

	@Test
	public void foo() {
		final var excel = SimpleExcel.of("E:/slicee/ws/项目/平安银行项目/平安项目-工作底稿.xlsx");
		final var dfm = excel.autoDetect("sql比对1").collect(DFrame.dfmclc2);
		// println(dfm);
		final var ai = new AtomicInteger(0);
		final var sqlhome = "E:/slicee/ws/项目/平安银行项目/sqls";
		dfm.forEach(e -> {
			final var i = ai.getAndIncrement();
			println(i, "----------------------------------");
			final var old_sql = extract_sql(e.str("原脚本"));
			final var new_sql = extract_sql(e.str("新脚本"));
//			println("old_sql",i,old_sql);
//			println("new_sql",i,new_sql);
			final var sqlfilehome = new File(FT("$0/$1/", sqlhome, i)); // sql 脚本目录
			sqlfilehome.mkdirs(); // 创建目录文件
			FileSystem.write(FT("$0/old_sql.sql", sqlfilehome.getAbsolutePath()), "utf-8", () -> old_sql);
			FileSystem.write(FT("$0/new_sql.sql", sqlfilehome.getAbsolutePath()), "utf-8", () -> new_sql);
		});
		System.out.println("----------------------------------");
		excel.close();
	}

}
